package Models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import utils.GetConnection;

public class Product {
	private int id;
	private String name;
	private double price;
	private int quantity;
	
	public int getQuantity() {
		return quantity;
	}

	public void setQuantity(int quantity) {
		this.quantity = quantity;
	}

	public Product(long id){
		PreparedStatement stm = null;
		try {
			String req = "select * from products where id = ?;";
			stm = GetConnection.getConnection().prepareStatement(req);			
			stm.setLong(1, id);
			ResultSet res = stm.executeQuery();
			if(res.next()){
				this.id = res.getInt("id");
				this.name = res.getString("name");
				this.price = res.getDouble("price");
				this.quantity = res.getInt("quantity");
			}
		}
		catch (SQLException e) {e.printStackTrace();}
		finally{
			try {
				if(stm != null) stm.close();
			} catch (SQLException e) {}
		}
	}
	
	public int getId() {
		return id;
	}
	
	
	
	public String getName() {
		return name;
	}



	public void setName(String name) {
		this.name = name;
	}



	public double getPrice() {
		return price;
	}



	public void setPrice(double price) {
		this.price = price;
	}



	public void setId(int id) {
		this.id = id;
	}


	public Product(){}
	
	public Product(int id, String name, double price,int quantity) {
		this.id = id;
		this.name = name;
		this.price = price;
		this.quantity = quantity;
	}
	
	public static List<Product> loadSql() {
		return loadSql("");
	}
	
	public static Product loadSql(int id) {
		String sqlWhere =  "id = " + id;
		
		return loadSql(sqlWhere).get(0);
	}
	
	public static List<Product> loadSql(String sqlWhere) {
		List<Product> products = new ArrayList<Product>();
		Connection conn = CustomConnection.getConnection();
		
		try {
			try {
				Class.forName("com.mysql.jdbc.Driver");
			}
			catch (Exception e) { System.out.println(e); }
			
			Statement stmt = null;
			ResultSet rs = null;

			stmt = conn.createStatement();
			rs = stmt.executeQuery("SELECT * FROM PRODUCTS" + ((sqlWhere.compareTo("") != 0) ? " WHERE " : "") + sqlWhere);
			
			
			while(rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				double price = rs.getDouble("price");
				int quantity = rs.getInt("quantity");
				
				products.add(new Product(id, name, price,quantity));
			}
		}
		catch (SQLException e) {
			System.out.println(e);
		}
		finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
		return products;
	}
	
	public static boolean save(Product product) {
		Connection conn = CustomConnection.getConnection();
		
		try {		
			PreparedStatement stmt = null;

			stmt = conn.prepareStatement("INSERT INTO PRODUCTS (name, price,quantity) VALUES (?,?,?);");
			stmt.setString(1, product.getName());
			stmt.setDouble(2, product.getPrice());
			stmt.setInt(3, product.getQuantity());
			if(stmt.executeUpdate() != 0)
				return true;
		}
		catch (SQLException e) {
			System.out.println(e);
		}
		finally {
			try {if(conn != null) conn.close();}
			catch (SQLException e) {}
		}
		
		return false;
	}
	
	public boolean update(){
		String req = "update products set name = ?, price = ?, quantity = ? where id = ?; ";
		PreparedStatement stm = null;
		try {
			stm = GetConnection.preparedStatement(req);
			stm.setString(1, name);
			stm.setDouble(2, price);
			stm.setDouble(3, quantity);
			stm.setInt(4, id);
			if(stm.executeUpdate() != 0)
				return true;
		} 
		catch (SQLException e) {e.printStackTrace();}
		finally{
			try {if(stm != null) stm.close();}
			catch (SQLException e) {}
		}
		
		return false;
	}
	
	public static void delete(Product product) {
		Connection conn = CustomConnection.getConnection();
		
		try {
			try {
				Class.forName("com.mysql.jdbc.Driver");
			}
			catch (Exception e) { System.out.println(e); }
			
			Statement stmt = null;

			stmt = conn.createStatement();
			stmt.executeUpdate("DELETE FROM PRODUCTS_PROVIDERS WHERE product = " + product.getId());
			
			stmt = conn.createStatement();
			stmt.executeUpdate("DELETE FROM PRODUCTS WHERE id = " + product.getId());
		}
		catch (SQLException e) {
			System.out.println(e);
		}
		finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public boolean hasEnough(int qte){
		if( quantity > qte)
			return true;
		else
			return false;
	}
	
	public boolean removeProductFromStock(int qte){
		String req = "update products set quantity = quantity - ? where id = ?";
		PreparedStatement stm = null;
		
		try {
			stm = GetConnection.preparedStatement(req);
			stm.setInt(1, qte);
			stm.setInt(2, id);
			if(stm.executeUpdate() != 0){
				quantity -= qte;
				return true;
			}else
				return false;
		} 
		catch (SQLException e) {e.printStackTrace();}
		finally{
			try {if( stm!=null) stm.close();} 
			catch (SQLException e) {}
		}
		
		return false;
	}
}
